﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DjModel;
using MySql.Data.MySqlClient;
using System.Data;
namespace DjDal
{

    public class DataProvider
    {
        #region dj_posts

        /// <summary>
        /// 得到当前页文章列表
        /// </summary>
        /// <param name="db"></param>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public static IList<PostInfo> GetPostList(DbHelper db,int pageindex,int pagesize, PostSearchInfo postsearch)
        {
            string where = "";
            if (postsearch != null)
            {
                switch (postsearch.Status)
                {
                    case PostGetEnum.Normal:
                        where += " where post_status=0";
                        break;
                    case PostGetEnum.Manager:
                        where += " where post_status in(0,1)";
                        break;
                    case PostGetEnum.Delete:
                        where += " where post_status in(0,1,2)";
                        break;
                    default:
                        where += " where post_status=0";
                        break;
                }
                if (!string.IsNullOrEmpty(postsearch.Author))
                {
                    where +=" and post_author like '%"+postsearch.Author+"%'";
                }
                if (!string.IsNullOrEmpty(postsearch.Title))
                {
                    where += " and post_title like '%" + postsearch.Title + "%'";
                }
                if (postsearch.STime != DateTime.MinValue)
                {
                    where += " and post_date>=" + postsearch.STime.ToString();
                }
                if (postsearch.ETime != DateTime.MinValue)
                {
                    where += " and post_date<=" + postsearch.ETime.ToString();
                }
                if (!string.IsNullOrEmpty(postsearch.PostID))
                {
                    where += " and ID in ("+postsearch.PostID+")";
                }
            }
            string sql = string.Format("select ID, post_author,post_date,post_content,post_title,post_excerpt,post_status,comment_count,read_count,tag_string, cat_string from dj_posts {2} order by post_date desc limit {0},{1} ",(pageindex - 1) * pagesize, pagesize,where);
            IList<PostInfo> postlist = null;
            using (MySqlDataReader dr = db.ExecuteReader(sql))
            {
                postlist = new List<PostInfo>();
                while (dr.Read())
                {
                    PostInfo post = new PostInfo();
                    post.ID = dr.GetInt32("ID");
                    post.Author = dr.GetString("post_author");
                    post.CreateDate = dr.GetDateTime("post_date");
                    post.Content = dr.GetString("post_content");
                    post.Title = dr.GetString("post_title");
                    post.Excerpt = dr.GetString("post_excerpt");
                    post.PostStatus = dr.GetInt32("post_status");
                    post.CommentCount = dr.GetInt32("comment_count");
                    post.ReadCount = dr.GetInt32("read_count");
                    post.TagString = dr.GetString("tag_string");
                    post.CatString = dr.GetString("cat_string");
                    postlist.Add(post);
                }
            }
            return postlist;
        }

        /// <summary>
        /// 根据ID得到具体文章
        /// </summary>
        /// <param name="db"></param>
        /// <param name="postID"></param>
        /// <param name="penum"></param>
        /// <returns></returns>
        public static PostInfo GetPostByID(DbHelper db, int postID,PostGetEnum penum)
        {
            string where = "";
            switch (penum)
            {
                case PostGetEnum.Normal:
                    where += " where post_status=0";
                    break;
                case PostGetEnum.Manager:
                    where += " where post_status in(0,1)";
                    break;
                case PostGetEnum.Delete:
                    where += " where post_status in(0,1,2)";
                    break;
                default:
                    where += " where post_status=0";
                    break;
            }
            where += " and ID = "+postID.ToString();
            string sql = "select ID, post_author,post_date,post_content,post_title,post_excerpt,post_status,comment_count,read_count,tag_string, cat_string from dj_posts " + where;
            PostInfo post = null;
            using (MySqlDataReader dr = db.ExecuteReader(sql))
            {
                if (dr.Read())
                {
                    post = new PostInfo();
                    post.ID = dr.GetInt32("ID");
                    post.Author = dr.GetString("post_author");
                    post.CreateDate = dr.GetDateTime("post_date");
                    post.Content = dr.GetString("post_content");
                    post.Title = dr.GetString("post_title");
                    post.Excerpt = dr.GetString("post_excerpt");
                    post.PostStatus = dr.GetInt32("post_status");
                    post.CommentCount = dr.GetInt32("comment_count");
                    post.ReadCount = dr.GetInt32("read_count");
                    post.TagString = dr.GetString("tag_string");
                    post.CatString = dr.GetString("cat_string");
                }
            }
            return post;
        }

        /// <summary>
        /// 更新文章评论数量
        /// </summary>
        /// <param name="db"></param>
        /// <param name="postid"></param>
        public static void UpdatePostComment(DbHelper db, int postid)
        {
            string sql = "update dj_posts set comment_count = comment_count+1 where id = "+postid;
            db.ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 更新文章阅读数量
        /// </summary>
        /// <param name="db"></param>
        /// <param name="postid"></param>
        public static void UpdatePostRead(DbHelper db, int postid)
        {
            string sql = "update dj_posts set read_count = read_count+1 where id = " + postid;
            db.ExecuteNonQuery(sql);
        }
        

        
        /// <summary>
        /// 添加文章
        /// </summary>
        /// <param name="db"></param>
        /// <param name="post"></param>
        public static int AddPost(DbHelper db, PostInfo post)
        {
            string sql = @"insert into dj_posts (post_author,post_date,post_content,post_title,post_excerpt,post_status,comment_count,tag_string, cat_string) values 
                (@author,@date,@content,@title,@excerpt,@status,@count,@tagstring,@catstring); select @@IDENTITY;";
            MySqlParameter[] pars = new MySqlParameter[]
            {
                DbHelper.MakeInParameter("@author",post.Author),
                DbHelper.MakeInParameter("@date",post.CreateDate),
                DbHelper.MakeInParameter("@content",post.Content),
                DbHelper.MakeInParameter("@title",post.Title),
                DbHelper.MakeInParameter("@excerpt",post.Excerpt),
                DbHelper.MakeInParameter("@status",post.PostStatus),
                DbHelper.MakeInParameter("@count",post.CommentCount),
                DbHelper.MakeInParameter("@tagstring",post.TagString),
                DbHelper.MakeInParameter("@catstring",post.CatString)
            };
            return  Convert.ToInt32(db.ExecuteScalar(sql, pars));
        }

        /// <summary>
        /// 更新文章
        /// </summary>
        /// <param name="db"></param>
        /// <param name="post"></param>
        public static void UpdatePost(DbHelper db, PostInfo post)
        {
            string sql = @"update dj_posts set post_content=@content, post_title = @title,post_excerpt=@excerpt,post_status=@status where id = @postid";
            MySqlParameter[] pars = new MySqlParameter[]
            {
                DbHelper.MakeInParameter("@content",post.Content),
                DbHelper.MakeInParameter("@title",post.Title),
                DbHelper.MakeInParameter("@excerpt",post.Excerpt),
                DbHelper.MakeInParameter("@status",post.PostStatus),
                DbHelper.MakeInParameter("@postid",post.ID)
            };
            db.ExecuteNonQuery(sql, pars);
        }


        #endregion

        #region dj_terms

        /// <summary>
        /// 分类列表获取
        /// </summary>
        /// <param name="db"></param>
        /// <param name="termgroup"></param>
        /// <returns></returns>
        public static IList<TermInfo> GetTermList(DbHelper db, int termgroup)
        {
            string sql = string.Format("select term_ID, term_name, term_group, term_pos, postnum from dj_terms where term_group = {0} order by term_pos",termgroup);
            IList<TermInfo> termlist = null;
            using (MySqlDataReader dr = db.ExecuteReader(sql))
            {
                termlist = new List<TermInfo>();
                while (dr.Read())
                {
                    TermInfo term = new TermInfo();
                    term.TermID = dr.GetInt32("term_ID");
                    term.TermName = dr.GetString("term_name");
                    term.TermGroup = dr.GetInt32("term_group");
                    term.TermPos = dr.GetInt32("term_pos");
                    term.PostNum = dr.GetInt32("postnum");
                    termlist.Add(term);
                }
            }
            return termlist;
        }

        /// <summary>
        /// 添加分类
        /// </summary>
        /// <param name="db"></param>
        /// <param name="term"></param>
        public static int AddTerm(DbHelper db, TermInfo term)
        {
            string sql = "insert into dj_terms (term_name, term_group, term_pos, postnum) values(@name,@group,@pos,@postnum);select @@IDENTITY;";
            MySqlParameter[] pars = new MySqlParameter[]
            {
                DbHelper.MakeInParameter("@name",term.TermName),
                DbHelper.MakeInParameter("@group",term.TermGroup),
                DbHelper.MakeInParameter("@pos",term.TermPos),
                DbHelper.MakeInParameter("@postnum",term.PostNum)
            };
            return Convert.ToInt32(db.ExecuteScalar(sql, pars));
        }

        /// <summary>
        /// 更新分类标签下文章数量
        /// </summary>
        /// <param name="db"></param>
        /// <param name="termid"></param>
        public static void UpdateTermPostNum(DbHelper db,int termid)
        {
            string sql = "update dj_terms set postnum=postnum+1 where term_id = " + termid;
            db.ExecuteNonQuery(sql);
        }

        #endregion

        #region dj_termrelation

        /// <summary>
        /// 得到单篇文章的相关分类和标签
        /// </summary>
        /// <param name="db"></param>
        /// <param name="postid"></param>
        /// <returns></returns>
        public static DataTable GetTermIDTable(DbHelper db, string postid)
        {
            string sql = string.Format("select term_ID from dj_termrelation where post_ID = {0}", postid);
            return db.ExecuteDatatable(sql);
        }

        /// <summary>
        /// 得到分类或标签下的所有文章ID列表
        /// </summary>
        /// <param name="db"></param>
        /// <param name="termid"></param>
        /// <returns></returns>
        public static DataTable GetPostIDTable(DbHelper db, string termid)
        {
            string sql = string.Format("select post_ID from dj_termrelation where term_ID = {0}", termid);
            return db.ExecuteDatatable(sql);
        }

        /// <summary>
        /// 添加文章分类关联信息
        /// </summary>
        /// <param name="db"></param>
        /// <param name="termid"></param>
        /// <param name="postid"></param>
        public static void AddTermRelation(DbHelper db, int termid, int postid)
        {
            string sql = "insert into dj_termrelation (post_id, term_id) values(@postid,@termid)";
            MySqlParameter[] pars = new MySqlParameter[]
            {
                DbHelper.MakeInParameter("postid",postid),
                DbHelper.MakeInParameter("termid",termid)
            };
            db.ExecuteNonQuery(sql, pars);
        }
        #endregion

        #region dj_comment

        /// <summary>
        /// 得到文章评论
        /// </summary>
        /// <param name="db"></param>
        /// <param name="postid"></param>
        /// <returns></returns>
        public static IList<CommentInfo> GetPostComment(DbHelper db, int postid)
        {
            string sql = "select ID ,post_ID,comment_author,comment_email,comment_url,comment_date,comment_content,comment_parentID from dj_comment where post_ID=@postid and comment_status =0 order by comment_date asc";
            MySqlParameter[] pars = new MySqlParameter[]
            {
                DbHelper.MakeInParameter("@postid",postid)
            };
            IList<CommentInfo> commentlist = new List<CommentInfo>();
            using (MySqlDataReader dr = db.ExecuteReader(sql, pars))
            {
                while (dr.Read())
                {
                    CommentInfo comment = new CommentInfo();
                    comment.ID = dr.GetInt32("ID");
                    comment.PostID = dr.GetInt32("post_ID");
                    comment.Author = dr.GetString("comment_author");
                    comment.Email = dr.GetString("comment_email");
                    comment.URL = dr.GetString("comment_url");
                    comment.CommentDate = dr.GetDateTime("comment_date");
                    comment.Content = dr.GetString("comment_content");
                    comment.ParentID = dr.GetInt32("comment_parentID");
                    commentlist.Add(comment);
                }
            }
            return commentlist;
        }

        /// <summary>
        /// 得到最新评论例表
        /// </summary>
        /// <param name="db"></param>
        /// <param name="num"></param>
        /// <returns></returns>
        public static IList<CommentInfo> GetLastComment(DbHelper db, int num)
        {
            string sql = "select a.ID ,a.post_ID,a.comment_author,a.comment_email,a.comment_url,a.comment_date,a.comment_content,a.comment_parentID,b.post_title from dj_comment a,dj_posts b where a.comment_status =0 and  a.post_ID>0 and a.post_ID = b.ID order by a.comment_date desc limit 0," + num;
            IList<CommentInfo> commentlist = new List<CommentInfo>();
            using (MySqlDataReader dr = db.ExecuteReader(sql))
            {
                while (dr.Read())
                {
                    CommentInfo comment = new CommentInfo();
                    comment.ID = dr.GetInt32("ID");
                    comment.PostID = dr.GetInt32("post_ID");
                    comment.Author = dr.GetString("comment_author");
                    comment.Email = dr.GetString("comment_email");
                    comment.URL = dr.GetString("comment_url");
                    comment.CommentDate = dr.GetDateTime("comment_date");
                    comment.Content = dr.GetString("comment_content");
                    comment.ParentID = dr.GetInt32("comment_parentID");
                    comment.PostName = dr.GetString("post_title");
                    commentlist.Add(comment);
                }
            }
            return commentlist;
        }

        /// <summary>
        /// 添加评论
        /// </summary>
        /// <param name="db"></param>
        /// <param name="comment"></param>
        /// <returns></returns>
        public static void AddPostComment(DbHelper db, CommentInfo comment )
        {
            string sql = @"insert into dj_comment (post_ID,comment_author,comment_IP,comment_email,comment_url,comment_date,comment_content,comment_status,comment_parentID ) 
                        values(@postid,@author,@ip,@email,@url,@date,@content,@status,@parentid)";
            MySqlParameter[] pars = new MySqlParameter[]
            {
                DbHelper.MakeInParameter("@postid",comment.PostID),
                DbHelper.MakeInParameter("@author",comment.Author),
                DbHelper.MakeInParameter("@ip",comment.IP),
                DbHelper.MakeInParameter("@email",comment.Email),
                DbHelper.MakeInParameter("@url",comment.URL),
                DbHelper.MakeInParameter("@date",comment.CommentDate),
                DbHelper.MakeInParameter("@content",comment.Content),
                DbHelper.MakeInParameter("@status",comment.CommentStatus),
                DbHelper.MakeInParameter("@parentid",comment.ParentID)
            };
            db.ExecuteNonQuery(sql, pars);
        }

        #endregion
    }
}
